#Include "Protheus.ch"

User Function TstEdi()

Local aDados    := {}
Local aTotais   := {}
Local lProcessa := .F.

Processa({|| lProcessa := ConsDados(@aDados, @aTotais)}, "Edi Transmassa", "Consultando dados, aguarde...")

IF lProcessa
	Alert("Fim")
EndIF

Return Nil

// ----------------------------------------------------------------------------------- //

Static Function ConsDados(aDados, aTotais)

Local aRegistro := {}
Local cQuery    := ""
Local cAlias    := GetNextAlias()
Local lProcessa := .T.
Local nRecCount := 0
Local nPeso     := 0

cQuery += " SELECT F2_DOC," + CRLF
cQuery += 	" F2_SERIE," + CRLF
cQuery += 	" F2_CLIENTE," + CRLF
cQuery += 	" F2_LOJA," + CRLF
cQuery += 	" F2_EMISSAO," + CRLF
cQuery += 	" F2_VALBRUT," + CRLF
cQuery += 	" F2_VOLUME1," + CRLF
cQuery += 	" F2_PBRUTO," + CRLF
cQuery += 	" D2_COD," + CRLF
cQuery += 	" SUM(D2_QUANT) D2_QUANT," + CRLF
cQuery += 	" SUM(D2_TOTAL) D2_TOTAL," + CRLF
cQuery += 	" D2_PEDIDO," + CRLF
cQuery += 	" C6_ITEM" + CRLF
cQuery += " FROM SF2010 SF2" + CRLF
cQuery += 	" INNER JOIN SD2010 SD2 ON F2_DOC = D2_DOC" + CRLF
cQuery += 		" AND F2_SERIE = D2_SERIE" + CRLF
cQuery += 		" AND F2_CLIENTE = D2_CLIENTE" + CRLF
cQuery += 		" AND F2_LOJA = D2_LOJA" + CRLF
cQuery += 	" INNER JOIN SC6010 SC6 ON D2_PEDIDO = C6_NUM" + CRLF
cQuery += 		" AND D2_COD = C6_PRODUTO" + CRLF
cQuery += " WHERE F2_EMISSAO BETWEEN '20130405' AND '20130405'" + CRLF
cQuery += 	" AND F2_REDESP = '111773'" + CRLF
cQuery += 	" AND " + RetSqlFil("SF2,SD2,SC6") + CRLF
cQuery += 	" AND " + RetSqlDel("SF2,SD2,SC6") + CRLF
cQuery += " GROUP BY F2_DOC," + CRLF
cQuery += 	" F2_SERIE," + CRLF
cQuery += 	" F2_CLIENTE," + CRLF
cQuery += 	" F2_LOJA," + CRLF
cQuery += 	" F2_EMISSAO," + CRLF
cQuery += 	" F2_VALBRUT," + CRLF
cQuery += 	" F2_VOLUME1," + CRLF
cQuery += 	" F2_PBRUTO," + CRLF
cQuery += 	" D2_COD," + CRLF
cQuery +=	" D2_PEDIDO," + CRLF
cQuery += 	" C6_ITEM" + CRLF
cQuery += " ORDER BY F2_DOC, F2_SERIE, D2_PEDIDO, C6_ITEM"

MemoWrite("C:\RELMICROSIGA\TSTEDI.TXT", cQuery)

cQuery := ChangeQuery(cQuery)

dbUseArea(.T., "TOPCONN", TcGenQry( , , cQuery), cAlias, .T., .F.)

Count To nRecCount

IF nRecCount == 0
	lProcessa := .F.
	Return lProcessa
EndIF

(cAlias)->(dbGoTop())
ProcRegua(nRecCount)

dbSelectArea("SC6")
SC6->(dbSetOrder(1))  // C6_FILIAL+C6_NUM+C6_ITEM+C6_PRODUTO

While !(cAlias)->(Eof())

	IncProc()
	aRegistro := {}
	
	nPos := IF(Len(aTotais) > 0, aScan(aTotais, {|x| x[1] == (cAlias)->F2_DOC}), 0)
			
	IF nPos > 0
	
		IF RTrim(SB1->B1_GRUPO) == "3" .And. !(RTrim((cAlias)->D2_COD) $ "315500/310000")
			aTotais[nPos, 3] += (cAlias)->D2_QUANT
		Else

			IF SB1->B1_TIPCONV $ "D"
				aTotais[nPos, 3] += ((cAlias)->D2_QUANT / SB1->B1_CONV) * SB1->B1_PESBRU
			Else
				aTotais[nPos, 3] += ((cAlias)->D2_QUANT * SB1->B1_CONV) * SB1->B1_PESBRU
			EndIF
			
		EndIF
	
		aTotais[nPos, 2] += SC6->C6_UNSVEN
		
	Else
	
		IF RTrim(SB1->B1_GRUPO) == "3" .And. !(RTrim((cAlias)->D2_COD) $ "315500/310000")
			nPeso := (cAlias)->D2_QUANT
		Else

			IF SB1->B1_TIPCONV $ "D"
				nPeso := ((cAlias)->D2_QUANT / SB1->B1_CONV) * SB1->B1_PESBRU
			Else
				nPeso := ((cAlias)->D2_QUANT * SB1->B1_CONV) * SB1->B1_PESBRU
			EndIF
			
		EndIF
		
		AAdd(aTotais, {(cAlias)->F2_DOC, SC6->C6_UNSVEN, nPeso})
		
	EndIF
	
	AAdd(aRegistro, (cAlias)->F2_DOC)
	AAdd(aRegistro, (cAlias)->F2_SERIE)
	AAdd(aRegistro, (cAlias)->F2_CLIENTE)
	AAdd(aRegistro, (cAlias)->F2_LOJA)
	AAdd(aRegistro, SToD((cAlias)->F2_EMISSAO))
	AAdd(aRegistro, (cAlias)->F2_VALBRUT)
	AAdd(aRegistro, (cAlias)->D2_COD)
	AAdd(aRegistro, (cAlias)->D2_QUANT)
	AAdd(aRegistro, (cAlias)->D2_TOTAL)
	
	AAdd(aDados, aClone(aRegistro))

	(cAlias)->(dbSkip())

End

Return lProcessa

// ----------------------------------------------------------------------------------- //

cQuery += " SELECT F2_DOC," + CRLF
cQuery += 	" F2_SERIE," + CRLF
cQuery += 	" F2_CLIENTE," + CRLF
cQuery += 	" F2_LOJA," + CRLF
cQuery += 	" A1_NOME," + CRLF
cQuery += 	" A1_DDD," + CRLF
cQuery += 	" A1_TEL," + CRLF
cQuery += 	" A1_CGC," + CRLF
cQuery += 	" A1_END," + CRLF
cQuery += 	" A1_BAIRRO," + CRLF
cQuery += 	" A1_CEP," + CRLF
cQuery += 	" A1_MUN," + CRLF
cQuery += 	" A1_EST," + CRLF
cQuery += 	" A1_BOLETO," + CRLF
cQuery += 	" F2_EMISSAO," + CRLF
cQuery += 	" F2_VALBRUT," + CRLF
cQuery += 	" F2_VOLUME1," + CRLF
cQuery += 	" F2_PBRUTO," + CRLF
cQuery += 	" D2_COD," + CRLF
cQuery += 	" SUM(D2_QUANT) D2_QUANT," + CRLF
cQuery += 	" SUM(D2_TOTAL) D2_TOTAL," + CRLF
cQuery += 	" C5_PEDCLI" + CRLF
cQuery += " FROM SF2010 SF2" + CRLF
cQuery += 	" INNER JOIN SD2010 SD2 ON F2_DOC = D2_DOC" + CRLF
cQuery += 		" AND F2_SERIE = D2_SERIE" + CRLF
cQuery += 		" AND F2_CLIENTE = D2_CLIENTE" + CRLF
cQuery += 		" AND F2_LOJA = D2_LOJA" + CRLF
cQuery += 	" INNER JOIN SA1010 ON F2_CLIENTE = A1_COD" + CRLF
cQuery += 		" AND F2_LOJA = A1_LOJA" + CRLF
cQuery += 	" INNER JOIN SC5010 SC5 ON D2_PEDIDO = C5_NUM" + CRLF
cQuery += " WHERE F2_EMISSAO BETWEEN '20130405' AND '20130405'" + CRLF
cQuery += 	" AND F2_REDESP = '111773'" + CRLF
cQuery += 	" AND F2_FILIAL = '01'" + CRLF
cQuery += 	" AND D2_FILIAL = '01'" + CRLF
cQuery += 	"	AND C5_FILIAL = '01'" + CRLF
cQuery += 	" AND SF2.D_E_L_E_T_ = ' '" + CRLF
cQuery += 	" AND SD2.D_E_L_E_T_ = ' '" + CRLF
cQuery += 	" AND SC5.D_E_L_E_T_ = ' '" + CRLF
cQuery += " GROUP BY F2_DOC," + CRLF
cQuery += 	" F2_SERIE," + CRLF
cQuery += 	" F2_CLIENTE," + CRLF
cQuery += 	" F2_LOJA," + CRLF
cQuery += 	" A1_NOME," + CRLF
cQuery += 	" A1_DDD," + CRLF
cQuery += 	" A1_TEL," + CRLF
cQuery += 	" A1_CGC," + CRLF
cQuery += 	" A1_END," + CRLF
cQuery += 	" A1_BAIRRO," + CRLF
cQuery += 	" A1_CEP," + CRLF
cQuery += 	" A1_MUN," + CRLF
cQuery +=	" A1_EST," + CRLF
cQuery += 	" A1_BOLETO," + CRLF
cQuery +=	" F2_EMISSAO," + CRLF
cQuery += 	" F2_VALBRUT," + CRLF
cQuery += 	" F2_VOLUME1," + CRLF
cQuery += 	" F2_PBRUTO," + CRLF
cQuery += 	" D2_COD," + CRLF
cQuery += 	" C5_PEDCLI" + CRLF
cQuery += 	" ORDER BY F2_DOC, F2_SERIE, D2_COD"